2T_데이터 분석을 위한 SQL 실습 (1) - WHERE IN, LIKE, JOIN

film테이블에서 2006년이나 2007년에 출시되었으면서,

PG등급이거나, G등급의 영화제목을 모두 출력하시오


In [1]:
import pymysql
db = pymysql.connect(
    "db.fastcamp.us",
    "root",
    "dkstncks",
    "sakila",
    charset='utf8',
)

In [2]:
film_df = pd.read_sql("SELECT * FROM film;", db)

In [3]:
film_df.head(1)


Out[3]:
film_id title description release_year language_id original_language_id rental_duration rental_rate length replacement_cost rating special_features last_update
0 1 ACADEMY DINOSAUR A Epic Drama of a Feminist And a Mad Scientist... 2006 1 None 6 0.99 86 20.99 PG Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42

In [5]:
SQL_QUERY = """
    SELECT *
    FROM film
    WHERE
        (release_year = 2006 OR release_year = 2007)
        AND (rating = "PG" OR rating = "G")
    ;
"""
pd.read_sql(SQL_QUERY, db)


Out[5]:
film_id title description release_year language_id original_language_id rental_duration rental_rate length replacement_cost rating special_features last_update
0 1 ACADEMY DINOSAUR A Epic Drama of a Feminist And a Mad Scientist... 2006 1 None 6 0.99 86 20.99 PG Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
1 2 ACE GOLDFINGER A Astounding Epistle of a Database Administrat... 2006 1 None 3 4.99 48 12.99 G Trailers,Deleted Scenes 2006-02-15 05:03:42
2 4 AFFAIR PREJUDICE A Fanciful Documentary of a Frisbee And a Lumb... 2006 1 None 5 2.99 117 26.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
3 5 AFRICAN EGG A Fast-Paced Documentary of a Pastry Chef And ... 2006 1 None 6 2.99 130 22.99 G Deleted Scenes 2006-02-15 05:03:42
4 6 AGENT TRUMAN A Intrepid Panorama of a Robot And a Boy who m... 2006 1 None 3 2.99 169 17.99 PG Deleted Scenes 2006-02-15 05:03:42
5 11 ALAMO VIDEOTAPE A Boring Epistle of a Butler And a Cat who mus... 2006 1 None 6 0.99 126 16.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
6 12 ALASKA PHANTOM A Fanciful Saga of a Hunter And a Pastry Chef ... 2006 1 None 6 0.99 136 22.99 PG Commentaries,Deleted Scenes 2006-02-15 05:03:42
7 13 ALI FOREVER A Action-Packed Drama of a Dentist And a Croco... 2006 1 None 4 4.99 150 21.99 PG Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
8 19 AMADEUS HOLY A Emotional Display of a Pioneer And a Technic... 2006 1 None 6 0.99 113 20.99 PG Commentaries,Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
9 22 AMISTAD MIDSUMMER A Emotional Character Study of a Dentist And a... 2006 1 None 6 2.99 85 10.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
10 25 ANGELS LIFE A Thoughtful Display of a Woman And a Astronau... 2006 1 None 3 2.99 74 15.99 G Trailers 2006-02-15 05:03:42
11 26 ANNIE IDENTITY A Amazing Panorama of a Pastry Chef And a Boat... 2006 1 None 3 0.99 86 15.99 G Commentaries,Deleted Scenes 2006-02-15 05:03:42
12 37 ARIZONA BANG A Brilliant Panorama of a Mad Scientist And a ... 2006 1 None 3 2.99 121 28.99 PG Trailers,Deleted Scenes 2006-02-15 05:03:42
13 39 ARMAGEDDON LOST A Fast-Paced Tale of a Boat And a Teacher who ... 2006 1 None 5 0.99 99 10.99 G Trailers 2006-02-15 05:03:42
14 41 ARSENIC INDEPENDENCE A Fanciful Documentary of a Mad Cow And a Woma... 2006 1 None 4 0.99 137 17.99 PG Trailers,Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
15 43 ATLANTIS CAUSE A Thrilling Yarn of a Feminist And a Hunter wh... 2006 1 None 6 2.99 170 15.99 G Behind the Scenes 2006-02-15 05:03:42
16 46 AUTUMN CROW A Beautiful Tale of a Dentist And a Mad Cow wh... 2006 1 None 3 4.99 108 13.99 G Trailers,Commentaries,Deleted Scenes,Behind th... 2006-02-15 05:03:42
17 50 BAKED CLEOPATRA A Stunning Drama of a Forensic Psychologist An... 2006 1 None 3 2.99 182 20.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
18 52 BALLROOM MOCKINGBIRD A Thrilling Documentary of a Composer And a Mo... 2006 1 None 6 0.99 173 29.99 G Commentaries,Deleted Scenes 2006-02-15 05:03:42
19 55 BARBARELLA STREETCAR A Awe-Inspiring Story of a Feminist And a Cat ... 2006 1 None 6 2.99 65 27.99 G Behind the Scenes 2006-02-15 05:03:42
20 56 BAREFOOT MANCHURIAN A Intrepid Story of a Cat And a Student who mu... 2006 1 None 6 2.99 129 15.99 G Trailers,Commentaries 2006-02-15 05:03:42
21 58 BEACH HEARTBREAKERS A Fateful Display of a Womanizer And a Mad Sci... 2006 1 None 6 2.99 122 16.99 G Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
22 61 BEAUTY GREASE A Fast-Paced Display of a Composer And a Moose... 2006 1 None 5 4.99 175 28.99 G Trailers,Commentaries 2006-02-15 05:03:42
23 63 BEDAZZLED MARRIED A Astounding Character Study of a Madman And a... 2006 1 None 6 0.99 73 21.99 PG Trailers,Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
24 65 BEHAVIOR RUNAWAY A Unbelieveable Drama of a Student And a Husba... 2006 1 None 3 4.99 100 20.99 PG Trailers,Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
25 72 BILL OTHERS A Stunning Saga of a Mad Scientist And a Foren... 2006 1 None 6 2.99 93 12.99 PG Trailers,Commentaries 2006-02-15 05:03:42
26 74 BIRCH ANTITRUST A Fanciful Panorama of a Husband And a Pioneer... 2006 1 None 4 4.99 162 18.99 PG Trailers,Commentaries,Deleted Scenes 2006-02-15 05:03:42
27 75 BIRD INDEPENDENCE A Thrilling Documentary of a Car And a Student... 2006 1 None 6 4.99 163 14.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
28 77 BIRDS PERDITION A Boring Story of a Womanizer And a Pioneer wh... 2006 1 None 5 4.99 61 15.99 G Trailers,Behind the Scenes 2006-02-15 05:03:42
29 78 BLACKOUT PRIVATE A Intrepid Yarn of a Pastry Chef And a Mad Sci... 2006 1 None 7 2.99 85 12.99 PG Trailers,Deleted Scenes 2006-02-15 05:03:42
... ... ... ... ... ... ... ... ... ... ... ... ... ...
342 914 TROUBLE DATE A Lacklusture Panorama of a Forensic Psycholog... 2006 1 None 6 2.99 61 13.99 PG Trailers,Commentaries,Behind the Scenes 2006-02-15 05:03:42
343 915 TRUMAN CRAZY A Thrilling Epistle of a Moose And a Boy who m... 2006 1 None 7 4.99 92 9.99 G Trailers,Commentaries 2006-02-15 05:03:42
344 916 TURN STAR A Stunning Tale of a Man And a Monkey who must... 2006 1 None 3 2.99 80 10.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
345 918 TWISTED PIRATES A Touching Display of a Frisbee And a Boat who... 2006 1 None 4 4.99 152 23.99 PG Trailers,Commentaries,Deleted Scenes 2006-02-15 05:03:42
346 919 TYCOON GATHERING A Emotional Display of a Husband And a A Shark... 2006 1 None 3 4.99 82 17.99 G Trailers,Commentaries,Deleted Scenes 2006-02-15 05:03:42
347 920 UNBREAKABLE KARATE A Amazing Character Study of a Robot And a Stu... 2006 1 None 3 0.99 62 16.99 G Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
348 924 UNFORGIVEN ZOOLANDER A Taut Epistle of a Monkey And a Sumo Wrestler... 2006 1 None 7 0.99 129 15.99 PG Trailers,Commentaries,Behind the Scenes 2006-02-15 05:03:42
349 928 UPTOWN YOUNG A Fateful Documentary of a Dog And a Hunter wh... 2006 1 None 5 2.99 84 16.99 PG Commentaries 2006-02-15 05:03:42
350 932 VALLEY PACKER A Astounding Documentary of a Astronaut And a ... 2006 1 None 3 0.99 73 21.99 G Commentaries,Deleted Scenes 2006-02-15 05:03:42
351 950 VOLUME HOUSE A Boring Tale of a Dog And a Woman who must Me... 2006 1 None 7 4.99 132 12.99 PG Commentaries 2006-02-15 05:03:42
352 952 WAGON JAWS A Intrepid Drama of a Moose And a Boat who mus... 2006 1 None 7 2.99 152 17.99 PG Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
353 954 WAKE JAWS A Beautiful Saga of a Feminist And a Composer ... 2006 1 None 7 4.99 73 18.99 G Trailers,Commentaries,Deleted Scenes,Behind th... 2006-02-15 05:03:42
354 955 WALLS ARTIST A Insightful Panorama of a Teacher And a Teach... 2006 1 None 7 4.99 135 19.99 PG Trailers,Behind the Scenes 2006-02-15 05:03:42
355 957 WAR NOTTING A Boring Drama of a Teacher And a Sumo Wrestle... 2006 1 None 7 4.99 80 26.99 G Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
356 958 WARDROBE PHANTOM A Action-Packed Display of a Mad Cow And a Ast... 2006 1 None 6 2.99 178 19.99 G Trailers,Commentaries 2006-02-15 05:03:42
357 959 WARLOCK WEREWOLF A Astounding Yarn of a Pioneer And a Crocodile... 2006 1 None 6 2.99 83 10.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
358 960 WARS PLUTO A Taut Reflection of a Teacher And a Database ... 2006 1 None 5 2.99 128 15.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
359 962 WASTELAND DIVINE A Fanciful Story of a Database Administrator A... 2006 1 None 7 2.99 85 18.99 PG Trailers,Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
360 963 WATCH TRACY A Fast-Paced Yarn of a Dog And a Frisbee who m... 2006 1 None 5 0.99 78 12.99 PG Trailers,Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
361 964 WATERFRONT DELIVERANCE A Unbelieveable Documentary of a Dentist And a... 2006 1 None 4 4.99 61 17.99 G Behind the Scenes 2006-02-15 05:03:42
362 965 WATERSHIP FRONTIER A Emotional Yarn of a Boat And a Crocodile who... 2006 1 None 6 0.99 112 28.99 G Commentaries 2006-02-15 05:03:42
363 966 WEDDING APOLLO A Action-Packed Tale of a Student And a Waitre... 2006 1 None 3 0.99 70 14.99 PG Trailers 2006-02-15 05:03:42
364 968 WEREWOLF LOLA A Fanciful Story of a Man And a Sumo Wrestler ... 2006 1 None 6 4.99 79 19.99 G Trailers,Behind the Scenes 2006-02-15 05:03:42
365 969 WEST LION A Intrepid Drama of a Butler And a Lumberjack ... 2006 1 None 4 4.99 159 29.99 G Trailers 2006-02-15 05:03:42
366 980 WIZARD COLDBLOODED A Lacklusture Display of a Robot And a Girl wh... 2006 1 None 4 4.99 75 12.99 PG Commentaries,Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
367 983 WON DARES A Unbelieveable Documentary of a Teacher And a... 2006 1 None 7 2.99 105 18.99 PG Behind the Scenes 2006-02-15 05:03:42
368 985 WONDERLAND CHRISTMAS A Awe-Inspiring Character Study of a Waitress ... 2006 1 None 4 4.99 111 19.99 PG Commentaries 2006-02-15 05:03:42
369 987 WORDS HUNTER A Action-Packed Reflection of a Composer And a... 2006 1 None 3 2.99 116 13.99 PG Trailers,Commentaries,Deleted Scenes 2006-02-15 05:03:42
370 991 WORST BANGER A Thrilling Drama of a Madman And a Dentist wh... 2006 1 None 4 2.99 185 26.99 PG Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
371 996 YOUNG LANGUAGE A Unbelieveable Yarn of a Boat And a Database ... 2006 1 None 6 0.99 183 9.99 G Trailers,Behind the Scenes 2006-02-15 05:03:42

372 rows × 13 columns


In [7]:
SQL_QUERY = """
    SELECT COUNT(*)
    FROM film
    WHERE
        release_year IN (2006, 2007)
        AND rating IN ("PG", "G")
    ;
"""
pd.read_sql(SQL_QUERY, db)


Out[7]:
COUNT(*)
0 372
  • pandas

In [10]:
is_pg_or_g = film_df.rating.isin(["PG", "G"])
is_2006_or_2007 = film_df.release_year.isin([2006, 2007])

In [11]:
film_df[is_pg_or_g & is_2006_or_2007]


Out[11]:
film_id title description release_year language_id original_language_id rental_duration rental_rate length replacement_cost rating special_features last_update
0 1 ACADEMY DINOSAUR A Epic Drama of a Feminist And a Mad Scientist... 2006 1 None 6 0.99 86 20.99 PG Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
1 2 ACE GOLDFINGER A Astounding Epistle of a Database Administrat... 2006 1 None 3 4.99 48 12.99 G Trailers,Deleted Scenes 2006-02-15 05:03:42
3 4 AFFAIR PREJUDICE A Fanciful Documentary of a Frisbee And a Lumb... 2006 1 None 5 2.99 117 26.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
4 5 AFRICAN EGG A Fast-Paced Documentary of a Pastry Chef And ... 2006 1 None 6 2.99 130 22.99 G Deleted Scenes 2006-02-15 05:03:42
5 6 AGENT TRUMAN A Intrepid Panorama of a Robot And a Boy who m... 2006 1 None 3 2.99 169 17.99 PG Deleted Scenes 2006-02-15 05:03:42
10 11 ALAMO VIDEOTAPE A Boring Epistle of a Butler And a Cat who mus... 2006 1 None 6 0.99 126 16.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
11 12 ALASKA PHANTOM A Fanciful Saga of a Hunter And a Pastry Chef ... 2006 1 None 6 0.99 136 22.99 PG Commentaries,Deleted Scenes 2006-02-15 05:03:42
12 13 ALI FOREVER A Action-Packed Drama of a Dentist And a Croco... 2006 1 None 4 4.99 150 21.99 PG Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
18 19 AMADEUS HOLY A Emotional Display of a Pioneer And a Technic... 2006 1 None 6 0.99 113 20.99 PG Commentaries,Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
21 22 AMISTAD MIDSUMMER A Emotional Character Study of a Dentist And a... 2006 1 None 6 2.99 85 10.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
24 25 ANGELS LIFE A Thoughtful Display of a Woman And a Astronau... 2006 1 None 3 2.99 74 15.99 G Trailers 2006-02-15 05:03:42
25 26 ANNIE IDENTITY A Amazing Panorama of a Pastry Chef And a Boat... 2006 1 None 3 0.99 86 15.99 G Commentaries,Deleted Scenes 2006-02-15 05:03:42
36 37 ARIZONA BANG A Brilliant Panorama of a Mad Scientist And a ... 2006 1 None 3 2.99 121 28.99 PG Trailers,Deleted Scenes 2006-02-15 05:03:42
38 39 ARMAGEDDON LOST A Fast-Paced Tale of a Boat And a Teacher who ... 2006 1 None 5 0.99 99 10.99 G Trailers 2006-02-15 05:03:42
40 41 ARSENIC INDEPENDENCE A Fanciful Documentary of a Mad Cow And a Woma... 2006 1 None 4 0.99 137 17.99 PG Trailers,Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
42 43 ATLANTIS CAUSE A Thrilling Yarn of a Feminist And a Hunter wh... 2006 1 None 6 2.99 170 15.99 G Behind the Scenes 2006-02-15 05:03:42
45 46 AUTUMN CROW A Beautiful Tale of a Dentist And a Mad Cow wh... 2006 1 None 3 4.99 108 13.99 G Trailers,Commentaries,Deleted Scenes,Behind th... 2006-02-15 05:03:42
49 50 BAKED CLEOPATRA A Stunning Drama of a Forensic Psychologist An... 2006 1 None 3 2.99 182 20.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
51 52 BALLROOM MOCKINGBIRD A Thrilling Documentary of a Composer And a Mo... 2006 1 None 6 0.99 173 29.99 G Commentaries,Deleted Scenes 2006-02-15 05:03:42
54 55 BARBARELLA STREETCAR A Awe-Inspiring Story of a Feminist And a Cat ... 2006 1 None 6 2.99 65 27.99 G Behind the Scenes 2006-02-15 05:03:42
55 56 BAREFOOT MANCHURIAN A Intrepid Story of a Cat And a Student who mu... 2006 1 None 6 2.99 129 15.99 G Trailers,Commentaries 2006-02-15 05:03:42
57 58 BEACH HEARTBREAKERS A Fateful Display of a Womanizer And a Mad Sci... 2006 1 None 6 2.99 122 16.99 G Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
60 61 BEAUTY GREASE A Fast-Paced Display of a Composer And a Moose... 2006 1 None 5 4.99 175 28.99 G Trailers,Commentaries 2006-02-15 05:03:42
62 63 BEDAZZLED MARRIED A Astounding Character Study of a Madman And a... 2006 1 None 6 0.99 73 21.99 PG Trailers,Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
64 65 BEHAVIOR RUNAWAY A Unbelieveable Drama of a Student And a Husba... 2006 1 None 3 4.99 100 20.99 PG Trailers,Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
71 72 BILL OTHERS A Stunning Saga of a Mad Scientist And a Foren... 2006 1 None 6 2.99 93 12.99 PG Trailers,Commentaries 2006-02-15 05:03:42
73 74 BIRCH ANTITRUST A Fanciful Panorama of a Husband And a Pioneer... 2006 1 None 4 4.99 162 18.99 PG Trailers,Commentaries,Deleted Scenes 2006-02-15 05:03:42
74 75 BIRD INDEPENDENCE A Thrilling Documentary of a Car And a Student... 2006 1 None 6 4.99 163 14.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
76 77 BIRDS PERDITION A Boring Story of a Womanizer And a Pioneer wh... 2006 1 None 5 4.99 61 15.99 G Trailers,Behind the Scenes 2006-02-15 05:03:42
77 78 BLACKOUT PRIVATE A Intrepid Yarn of a Pastry Chef And a Mad Sci... 2006 1 None 7 2.99 85 12.99 PG Trailers,Deleted Scenes 2006-02-15 05:03:42
... ... ... ... ... ... ... ... ... ... ... ... ... ...
913 914 TROUBLE DATE A Lacklusture Panorama of a Forensic Psycholog... 2006 1 None 6 2.99 61 13.99 PG Trailers,Commentaries,Behind the Scenes 2006-02-15 05:03:42
914 915 TRUMAN CRAZY A Thrilling Epistle of a Moose And a Boy who m... 2006 1 None 7 4.99 92 9.99 G Trailers,Commentaries 2006-02-15 05:03:42
915 916 TURN STAR A Stunning Tale of a Man And a Monkey who must... 2006 1 None 3 2.99 80 10.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
917 918 TWISTED PIRATES A Touching Display of a Frisbee And a Boat who... 2006 1 None 4 4.99 152 23.99 PG Trailers,Commentaries,Deleted Scenes 2006-02-15 05:03:42
918 919 TYCOON GATHERING A Emotional Display of a Husband And a A Shark... 2006 1 None 3 4.99 82 17.99 G Trailers,Commentaries,Deleted Scenes 2006-02-15 05:03:42
919 920 UNBREAKABLE KARATE A Amazing Character Study of a Robot And a Stu... 2006 1 None 3 0.99 62 16.99 G Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
923 924 UNFORGIVEN ZOOLANDER A Taut Epistle of a Monkey And a Sumo Wrestler... 2006 1 None 7 0.99 129 15.99 PG Trailers,Commentaries,Behind the Scenes 2006-02-15 05:03:42
927 928 UPTOWN YOUNG A Fateful Documentary of a Dog And a Hunter wh... 2006 1 None 5 2.99 84 16.99 PG Commentaries 2006-02-15 05:03:42
931 932 VALLEY PACKER A Astounding Documentary of a Astronaut And a ... 2006 1 None 3 0.99 73 21.99 G Commentaries,Deleted Scenes 2006-02-15 05:03:42
949 950 VOLUME HOUSE A Boring Tale of a Dog And a Woman who must Me... 2006 1 None 7 4.99 132 12.99 PG Commentaries 2006-02-15 05:03:42
951 952 WAGON JAWS A Intrepid Drama of a Moose And a Boat who mus... 2006 1 None 7 2.99 152 17.99 PG Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
953 954 WAKE JAWS A Beautiful Saga of a Feminist And a Composer ... 2006 1 None 7 4.99 73 18.99 G Trailers,Commentaries,Deleted Scenes,Behind th... 2006-02-15 05:03:42
954 955 WALLS ARTIST A Insightful Panorama of a Teacher And a Teach... 2006 1 None 7 4.99 135 19.99 PG Trailers,Behind the Scenes 2006-02-15 05:03:42
956 957 WAR NOTTING A Boring Drama of a Teacher And a Sumo Wrestle... 2006 1 None 7 4.99 80 26.99 G Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
957 958 WARDROBE PHANTOM A Action-Packed Display of a Mad Cow And a Ast... 2006 1 None 6 2.99 178 19.99 G Trailers,Commentaries 2006-02-15 05:03:42
958 959 WARLOCK WEREWOLF A Astounding Yarn of a Pioneer And a Crocodile... 2006 1 None 6 2.99 83 10.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
959 960 WARS PLUTO A Taut Reflection of a Teacher And a Database ... 2006 1 None 5 2.99 128 15.99 G Commentaries,Behind the Scenes 2006-02-15 05:03:42
961 962 WASTELAND DIVINE A Fanciful Story of a Database Administrator A... 2006 1 None 7 2.99 85 18.99 PG Trailers,Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
962 963 WATCH TRACY A Fast-Paced Yarn of a Dog And a Frisbee who m... 2006 1 None 5 0.99 78 12.99 PG Trailers,Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
963 964 WATERFRONT DELIVERANCE A Unbelieveable Documentary of a Dentist And a... 2006 1 None 4 4.99 61 17.99 G Behind the Scenes 2006-02-15 05:03:42
964 965 WATERSHIP FRONTIER A Emotional Yarn of a Boat And a Crocodile who... 2006 1 None 6 0.99 112 28.99 G Commentaries 2006-02-15 05:03:42
965 966 WEDDING APOLLO A Action-Packed Tale of a Student And a Waitre... 2006 1 None 3 0.99 70 14.99 PG Trailers 2006-02-15 05:03:42
967 968 WEREWOLF LOLA A Fanciful Story of a Man And a Sumo Wrestler ... 2006 1 None 6 4.99 79 19.99 G Trailers,Behind the Scenes 2006-02-15 05:03:42
968 969 WEST LION A Intrepid Drama of a Butler And a Lumberjack ... 2006 1 None 4 4.99 159 29.99 G Trailers 2006-02-15 05:03:42
979 980 WIZARD COLDBLOODED A Lacklusture Display of a Robot And a Girl wh... 2006 1 None 4 4.99 75 12.99 PG Commentaries,Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
982 983 WON DARES A Unbelieveable Documentary of a Teacher And a... 2006 1 None 7 2.99 105 18.99 PG Behind the Scenes 2006-02-15 05:03:42
984 985 WONDERLAND CHRISTMAS A Awe-Inspiring Character Study of a Waitress ... 2006 1 None 4 4.99 111 19.99 PG Commentaries 2006-02-15 05:03:42
986 987 WORDS HUNTER A Action-Packed Reflection of a Composer And a... 2006 1 None 3 2.99 116 13.99 PG Trailers,Commentaries,Deleted Scenes 2006-02-15 05:03:42
990 991 WORST BANGER A Thrilling Drama of a Madman And a Dentist wh... 2006 1 None 4 2.99 185 26.99 PG Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42
995 996 YOUNG LANGUAGE A Unbelieveable Yarn of a Boat And a Database ... 2006 1 None 6 0.99 183 9.99 G Trailers,Behind the Scenes 2006-02-15 05:03:42

372 rows × 13 columns


In [13]:
film_df[is_pg_or_g & is_2006_or_2007].count()


Out[13]:
film_id                 372
title                   372
description             372
release_year            372
language_id             372
original_language_id      0
rental_duration         372
rental_rate             372
length                  372
replacement_cost        372
rating                  372
special_features        372
last_update             372
dtype: int64

In [ ]:

film 테이블에서 설명에 "Boring"이라는 텍스트가 포함되면서, 렌탈 비용이 0.99인

영화 제목, 설명, 렌탈 비용을 모두 출력하세요.


In [14]:
film_df.head(1)


Out[14]:
film_id title description release_year language_id original_language_id rental_duration rental_rate length replacement_cost rating special_features last_update
0 1 ACADEMY DINOSAUR A Epic Drama of a Feminist And a Mad Scientist... 2006 1 None 6 0.99 86 20.99 PG Deleted Scenes,Behind the Scenes 2006-02-15 05:03:42

In [16]:
SQL_QUERY = """
    SELECT title, description, rental_rate
    FROM film
    WHERE
        description LIKE "%Boring%"
        AND rental_rate = 0.99
    ;    
"""
pd.read_sql(SQL_QUERY, db)


Out[16]:
title description rental_rate
0 ALAMO VIDEOTAPE A Boring Epistle of a Butler And a Cat who mus... 0.99
1 ARMY FLINTSTONES A Boring Saga of a Database Administrator And ... 0.99
2 BLOOD ARGONAUTS A Boring Drama of a Explorer And a Man who mus... 0.99
3 CATCH AMISTAD A Boring Reflection of a Lumberjack And a Femi... 0.99
4 COMANCHEROS ENEMY A Boring Saga of a Lumberjack And a Monkey who... 0.99
5 CURTAIN VIDEOTAPE A Boring Reflection of a Dentist And a Mad Cow... 0.99
6 GRAPES FURY A Boring Yarn of a Mad Cow And a Sumo Wrestler... 0.99
7 GUN BONNIE A Boring Display of a Sumo Wrestler And a Husb... 0.99
8 JEOPARDY ENCINO A Boring Panorama of a Man And a Mad Cow who m... 0.99
9 JUNGLE CLOSER A Boring Character Study of a Boy And a Woman ... 0.99
10 LOATHING LEGALLY A Boring Epistle of a Pioneer And a Mad Scient... 0.99
11 LOVERBOY ATTACKS A Boring Story of a Car And a Butler who must ... 0.99
12 MARS ROMAN A Boring Drama of a Car And a Dog who must Suc... 0.99
13 MOTHER OLEANDER A Boring Tale of a Husband And a Boy who must ... 0.99
14 NONE SPIKING A Boring Reflection of a Secret Agent And a As... 0.99
15 OLEANDER CLUE A Boring Story of a Teacher And a Monkey who m... 0.99
16 POLISH BROOKLYN A Boring Character Study of a Database Adminis... 0.99
17 SIEGE MADRE A Boring Tale of a Frisbee And a Crocodile who... 0.99
18 STRAIGHT HOURS A Boring Panorama of a Secret Agent And a Girl... 0.99
19 TIMBERLAND SKY A Boring Display of a Man And a Dog who must R... 0.99
20 TREATMENT JEKYLL A Boring Story of a Teacher And a Student who ... 0.99

In [17]:
is_099 = film_df.rental_rate == 0.99
is_boring = film_df.description.str.contains("Boring")

In [18]:
film_df[is_099 & is_boring].count()


Out[18]:
film_id                 21
title                   21
description             21
release_year            21
language_id             21
original_language_id     0
rental_duration         21
rental_rate             21
length                  21
replacement_cost        21
rating                  21
special_features        21
last_update             21
dtype: int64
  • rental_rate 에 unique한 값들은 어떤게 있었을까? 0.99, 1.99, 2.99...

In [19]:
film_df.rental_rate.unique()


Out[19]:
array([ 0.99,  4.99,  2.99])

In [20]:
SQL_QUERY = """
    SELECT
        DISTINCT rental_rate
    FROM film
    ORDER BY rental_rate
    ;
"""
pd.read_sql(SQL_QUERY, db)


Out[20]:
rental_rate
0 0.99
1 2.99
2 4.99

In [ ]:

film테이블에서 등급으로 그룹을 묶어서, 각 등급별 갯수, 평균 렌탈 비용을 모두 출력하세요


In [22]:
SQL_QUERY = """
    SELECT rating, COUNT(*) "total_films", AVG(rental_rate) "average_rental_rate"  
    FROM film
    GROUP BY rating
    ORDER BY average_rental_rate
    ;
"""
pd.read_sql(SQL_QUERY, db)


Out[22]:
rating total_films average_rental_rate
0 G 178 2.888876
1 R 195 2.938718
2 NC-17 210 2.970952
3 PG-13 223 3.034843
4 PG 194 3.051856

In [24]:
SQL_QUERY = """
    SELECT
        rating,
        COUNT(*) "total_films",
        AVG(rental_rate) "average_rental_rate"
    FROM film
    GROUP BY
        1
    ORDER BY 3
    ;
"""
pd.read_sql(SQL_QUERY, db)


Out[24]:
rating total_films average_rental_rate
0 G 178 2.888876
1 R 195 2.938718
2 NC-17 210 2.970952
3 PG-13 223 3.034843
4 PG 194 3.051856

In [ ]:


In [25]:
film_df.groupby("rating").agg({
        "film_id": {"total films": np.size},
        "rental_rate": {"average_rental_rate": np.mean},
    })


Out[25]:
film_id rental_rate
total films average_rental_rate
rating
G 178 2.888876
NC-17 210 2.970952
PG 194 3.051856
PG-13 223 3.034843
R 195 2.938718